![RDS for PostgreSQLで14.2がサポートされたので、mysql_fdwを使ってMySQLへ外部接続してみた](https://devio2023-media.developers.io/wp-content/uploads/2019/05/amazon-rds.png)
RDS for PostgreSQLで14.2がサポートされたので、mysql_fdwを使ってMySQLへ外部接続してみた
いわさです。
RDS for PostgreSQLで新たなマイナーバージョン14.2, 13.6などが利用出来るようになりました。
RDS for PostgreSQL 14.2, 13.6では、mysql_fdw拡張機能がサポートされており、MySQ/MariaDBへの外部接続が出来ます。
EnterpriseDB/mysql_fdw: PostgreSQL foreign data wrapper for MySQL
ためしてみる
以下のようにRDSでPostgreSQL 14.2とMySQLを作成しておき、PostgreSQLからMySQLへアクセスしてみたいと思います。
間のセキュリティグループ設定などは済んでいます。
MySQL側でテーブルなどを作成
MySQLでDBとテーブルを作成しておきます。
mysql> create database hoge; mysql> use hoge; mysql> create table piyo (id int, name varchar(10), updatetime datetime DEFAULT NULL); mysql> insert into piyo values (1, 'aaa1', '2021-10-27 9:00:00'); mysql> insert into piyo values (2, 'bbb1', '2021-10-27 9:00:00'); mysql> insert into piyo values (3, 'ccc1', '2021-10-27 9:00:00'); mysql> select * from piyo; +------+------+---------------------+ | id | name | updatetime | +------+------+---------------------+ | 1 | aaa1 | 2021-10-27 09:00:00 | | 2 | bbb1 | 2021-10-27 09:00:00 | | 3 | ccc1 | 2021-10-27 09:00:00 | +------+------+---------------------+ 3 rows in set (0.03 sec)
上記テーブルはユニークキーが設定されていません。
この状態だと、PostgreSQLからUPDATE, DELETE, INSERT操作をすることが出来ずSELECTしか出来ません。
postgres=> insert into mysqltbl values (4, 'ddd1', now()); ERROR: first column of remote table must be unique for INSERT/UPDATE/DELETE operation
ユニークキーを追加設定しておきます。
mysql> show columns from piyo; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int | YES | | NULL | | | name | varchar(10) | YES | | NULL | | | updatetime | datetime | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.04 sec) mysql> alter table piyo add unique (id); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from piyo; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | int | YES | UNI | NULL | | | name | varchar(10) | YES | | NULL | | | updatetime | datetime | YES | | NULL | | +------------+-------------+------+-----+---------+-------+ 3 rows in set (0.03 sec)
PostgreSQLで外部連携設定
PostgreSQL側では拡張機能の有効化、外部データの接続構成を設定、テーブルのマッピング、ロールへのアクセス許可を与えます。
ロールはpostgresqlなど特権ユーザーは利用できないようなので事前にテスト用のロールを作成しておきました。
postgres=> CREATE ROLE user1 WITH PASSWORD 'XXXXXXXX' LOGIN; postgres=> CREATE EXTENSION mysql_fdw; CREATE EXTENSION postgres=> CREATE SERVER hogemysql FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'database-2.cpnu9ipu74g4.ap-northeast-1.rds.amazonaws.com', port '3306'); CREATE SERVER postgres=> GRANT USAGE ON FOREIGN SERVER hogemysql to user1; GRANT postgres=> CREATE USER MAPPING FOR user1 SERVER hogemysql OPTIONS (username 'admin', password 'XXXXXXXX'); CREATE USER MAPPING postgres=> CREATE FOREIGN TABLE mysqltbl (id int, name text, updatetime timestamp) SERVER hogemysql OPTIONS (dbname 'hoge', table_name 'piyo'); CREATE FOREIGN TABLE postgres=> GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE mysqltbl TO user1; GRANT
PostgreSQLでデータ操作
postgres=> insert into mysqltbl values (4, 'ddd1', now()); INSERT 0 1 postgres=> select * from mysqltbl; id | name | updatetime +------+--------------------- 1 | aaa1 | 2021-10-27 09:00:00 2 | bbb1 | 2021-10-27 09:00:00 3 | ccc1 | 2021-10-27 09:00:00 4 | ddd1 | 2022-03-15 21:37:48 (4 rows)
PostgreSQL側のテーブルとMySQL側のテーブルを結合してみました。
postgres=> select a.id, a.name, b.name2, a.updatetime from mysqltbl a inner join fuga b on a.id = b.id; id | name | name2 | updatetime ----+------+-------+--------------------- 1 | aaa1 | aaa2 | 2021-10-27 09:00:00 2 | bbb1 | bbb2 | 2021-10-27 09:00:00 3 | ccc1 | ccc2 | 2021-10-27 09:00:00 (3 rows)
いけるな。
ちなみに、連携設定時のCREATE FOREIGN TABLE
マッピングが正しくないと、クエリ実行時にマッピングエラーが発生します。
postgres=> select * from mysqltbl; ERROR: failed to prepare the MySQL query: Unknown column 'a' in 'field list'
さいごに
本日はmysql_fdw
を使ってPostgreSQLからMySQLへ接続してみました。
PostgreSQLのForeign Data Wrapper(外部データラッパー?)を知らなかったのですが、oracle_fdw
やpostgres_fdw
もあったのですね。
また、今回のマイナーバージョンアップからtds_fdw
もサポートに追加され、Microsoft SQL Serverへの外部接続も出来るようになったみたいです。